﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Runtime.InteropServices;
using System.Web;

namespace DYSales.Web
{
    public class ExcelIO
    {
        public string errorMessage = string.Empty;
        public DataSet ImportExcel(string fileName, bool FirstSheet)
        {
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook workbook;
            try
            {
                workbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);
            }
            catch (COMException ex)
            {
                errorMessage = ex.Message;
                return null;
            }
            int n = workbook.Worksheets.Count;
            if (FirstSheet)
            {
                n = 1;
            }
            string[] SheetSet = new string[n];
            ArrayList al = new ArrayList();
            for (int i = 1; i <= n; i++)
            {
                SheetSet[i - 1] = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[i]).Name;
            }

            workbook.Close(null, null, null);
            xlApp.Quit();
            if (workbook != null)
            {
                Marshal.ReleaseComObject(workbook);
                workbook = null;
            }
            if (xlApp != null)
            {
                Marshal.ReleaseComObject(xlApp);
                xlApp = null;
            }
            GC.Collect();

            DataSet ds = new DataSet();
            string connStr12 = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + fileName + ";Extended Properties=Excel 12.0;";
            using (OleDbConnection conn = new OleDbConnection(connStr12))
            {
                conn.Open();
                OleDbDataAdapter da;
                for (int i = 1; i <= n; i++)
                {
                    string sql = "select * from [" + SheetSet[i - 1] + "$]";
                    da = new OleDbDataAdapter(sql, conn);
                    da.Fill(ds, SheetSet[i - 1]);
                    da.Dispose();
                }
                conn.Close();
                conn.Dispose();
            }
            //删除空行
            foreach (var item in ds.Tables)
            {
                var it = item as DataTable;
                for (int i = 0; i < it.Rows.Count; i++)
                {
                    DataRow dr = it.Rows[i];
                    if (dr.ItemArray.Where(c => c == DBNull.Value || string.IsNullOrEmpty(c.ToString())).Count().Equals(dr.ItemArray.Length))  
                    {
                        it.Rows.RemoveAt(i);
                        i--;
                    }
                }
            }
            return ds;
        }
    }
}